/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sistema.entidades;

import br.com.sistema.entidades.editor.Editor;
import br.com.sistema.excecoes.EntidadeNaoEncontrada;
import br.com.sistema.infraestrutura.ConnectionPool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Lauro
 */
public class Noticia extends Pagina{

    public Noticia(int id, String titulo) {
        super(id, titulo);
    }
    private Categoria categoria;
    private Date data;
    private Editor editor;

    public Noticia(int id) throws SQLException, EntidadeNaoEncontrada {
        super(id);
        buscar();
    }
    
    public static List<Noticia> getNoticiasSemMenu() throws SQLException {
        List<Noticia> noticias = new ArrayList<Noticia>();
        Connection conexao = ConnectionPool.getConnection();
        PreparedStatement query = conexao.prepareStatement(getSQLBuscarSemMenu());
        ResultSet result = query.executeQuery();
        while (result.next()) {
            Noticia noticia = new Noticia(
                    result.getInt("id"),
                    result.getString("titulo"));
            noticias.add(noticia);
        }
        
        return noticias;
    }
    
    //<editor-fold defaultstate="collapsed" desc="Metodos Getters & Setters.">
    public Categoria getCategoria() {
        return categoria;
    }

    public void setCategoria(Categoria categoria) {
        this.categoria = categoria;
    }

    public Date getData() {
        return data;
    }

    public void setData(Date data) {
        this.data = data;
    }

    public Editor getEditor() {
        return editor;
    }

    public void setEditor(Editor editor) {
        this.editor = editor;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="Strings SQL.">
    private String getSQLInsercao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO noticia ( ");
        sql.append("   id, ");
        sql.append("   categoria, ");
        sql.append("   data ");
        sql.append(" ) VALUES ( ");
        sql.append("   ?, ");
        sql.append("   ?, ");
        sql.append("   ? ");
        sql.append(" ) ");
        return sql.toString();
    }
    
    private String getSQLAtualizacao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE ");
        sql.append(" noticia ");
        sql.append(" SET ");
        sql.append(" categoria=?, ");
        sql.append(" data=? ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }
    
    private String getSQLExclusao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ");
        sql.append(" noticia ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }
    
    @Override
    protected String getSQLBuscarPorId() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT pagina.*, noticia.*, categoria.* ");
        sql.append(" FROM noticia ");
        sql.append(" INNER JOIN pagina ON pagina.id = noticia.id ");
        sql.append(" LEFT OUTER JOIN categoria ON categoria.id = noticia.categoria ");
        sql.append(" WHERE noticia.id = ? ");
        return sql.toString();
    }
    
    private static String getSQLBuscarSemMenu() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * ");
        sql.append(" FROM pagina ");
        sql.append(" INNER JOIN noticia ON pagina.id = noticia.id ");
        sql.append(" AND pagina.item_menu IS NULL ");
        return sql.toString();
    }
    // </editor-fold>
    
    // BEGIN: CRUD
    //<editor-fold defaultstate="expanded" desc="Metodos CRUD.">
    @Override
    public void buscar() throws SQLException, EntidadeNaoEncontrada {
        getConexao();
        PreparedStatement query = getConexao().prepareStatement(getSQLBuscarPorId());
        query.setInt(1, this.getId()); // id
        ResultSet result = query.executeQuery();
        if (result.next()) {
            this.setId(result.getInt("noticia.id"));
            this.setTitulo(result.getString("pagina.titulo"));
            this.setConteudo(result.getString("pagina.conteudo"));
            this.setCategoria(Categoria.getCategoria(result.getInt("categoria.id")));
            this.setData(new Date(result.getTimestamp("noticia.data").getTime()));
        }else{
            throw new EntidadeNaoEncontrada();
        }
    }

    @Override
    protected void inserir() throws SQLException {
        super.inserir();
        PreparedStatement query = getConexao().prepareStatement(getSQLInsercao(), Statement.RETURN_GENERATED_KEYS);
        query.setInt(1, this.getId());
        query.setInt(2, this.getCategoria().getId());
        query.setTimestamp(3, new Timestamp(this.getData().getTime()));
        query.executeUpdate();
    }

    @Override
    protected void atualizar() throws SQLException {
        super.atualizar();
        PreparedStatement query = getConexao().prepareStatement(getSQLAtualizacao());
        query.setInt(1, this.getCategoria().getId());
        query.setTimestamp(2, new Timestamp(this.getData().getTime()));
        query.setInt(3, this.getId()); // id
        query.executeUpdate();
    }
    
    @Override
    protected void excluir() throws SQLException{
        PreparedStatement query = getConexao().prepareStatement(getSQLExclusao());
        query.setInt(1, this.getId()); // id
        query.executeUpdate();
        super.excluir();
    }
    // </editor-fold>
    // END: CRUD
}
